package service.impl;

import entity.CourseDO;
import req.CourseRequest;
import req.StudentRequest;
import res.CourseDTO;
import res.TableDTO;
import service.CourseService;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class CourseServiceImpI implements CourseService {


    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int sno = rs.getInt("cno");
            String sname = rs.getString("cname");
            String ssex = rs.getString("credits");
            String sage = rs.getString("tno");
//            String sdept = rs.getString("sdept");
//            String sspe = rs.getString("sspe");
//            String sclass = rs.getString("sclass");
            oneRecord.addElement(sno);
            oneRecord.addElement(sname);
            oneRecord.addElement(ssex);
            oneRecord.addElement(sage);
//            oneRecord.addElement(sdept);
//            oneRecord.addElement(sspe);
//            oneRecord.addElement(sclass);
            data.addElement(oneRecord);
        }
        return data;

    }

    @Override
    public CourseDTO retrieveCourse(CourseRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from course ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where cname like '%" + request.getSearchKey().trim() + "%'");
        }
        sql.append("order by cno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        CourseDTO returnDTO = new CourseDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from course ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where cname like '%" + request.getSearchKey().trim() + "%'");
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean add(CourseDO courseDO) {
        StringBuilder sql = new StringBuilder();
        //修改版
        //sql.append(" insert into stu(sno,sname,ssex,sage,sdept,sspe,sclass) ");
        sql.append(" insert into course ");
        sql.append(" values(?,?,?,?)");

        PreparedStatement ps = null;
        Connection conn = null;
        //ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, courseDO.getCno());
            ps.setString(2, courseDO.getCname());
            ps.setString(3, courseDO.getCredits());
            ps.setString(4, courseDO.getTno());
//            ps.setString(5,studentDO.getDept());
//            ps.setString(6,studentDO.getSpe());
//            ps.setString(7,studentDO.getSclass());

            return ps.executeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
        //return false;
    }

    @Override
    public CourseDO getById(int selectCourseId) {
        StringBuilder sql = new StringBuilder("select * from course where cno = ? ");
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        CourseDO courseDO = new CourseDO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1, selectCourseId);
            rs = ps.executeQuery();
            while (rs.next()) {
                //Vector<Object> oneRecord = new Vector<>();
                int cno = rs.getInt("cno");
                String cname = rs.getString("cname");
                String credits = rs.getString("credits");
                String tno = rs.getString("tno");
                courseDO.setCno(cno);
                courseDO.setCname(cname);
                courseDO.setCredits(credits);
                courseDO.setTno(tno);
            }
            return courseDO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean update(CourseDO courseDO) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" update course set cname = ?,credits = ?,tno = ? ");
        sql.append(" where cno = ? ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            //ps.setString(1,studentDO.getNo());
            ps.setString(1, courseDO.getCname());
            ps.setString(2, courseDO.getCredits());
            ps.setString(3, courseDO.getTno());
            ps.setInt(4, courseDO.getCno());
//            ps.setString(5,studentDO.getSpe());
//            ps.setString(6,studentDO.getSclass());
//            ps.setInt(7,studentDO.getSno());

            return ps.executeUpdate() == 1;
            //查寻记录
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
        //return false;
    }

    @Override
    public boolean delete(int[] selectCourseIds) {
        StringBuilder sql = new StringBuilder();
        //修改版
        sql.append(" delete from course where cno in ( ");
        int length = selectCourseIds.length;
        for (int i = 0; i < selectCourseIds.length; i++) {
            if (i == length - 1) {
                sql.append(" ? ");
            } else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");

        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        //TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                //设置参数
                ps.setInt(i + 1, selectCourseIds[i]);
            }
            return ps.executeUpdate() == length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }
}
